﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>

		temFile = GetTempDirectory() & createUUID() & ".xls";
		cetUpdate = 0;
		cetInsert = 0;
		
		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labSummary", true);
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入CET成绩数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="CET成绩">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入CET成绩信息</p>
				</cfcatch>
			</cftry>

			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>

				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->						

				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "准考证")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "语言级别")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "缺考")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "总分")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "听力分数")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "阅读分数")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "写作分数")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "综合测试分数")><cfset columnChecked = false /></cfif>
				
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 准考证,语言级别,学号,缺考,总分,听力分数,阅读分数,写作分数,综合测试分数</p>
				</cfif>
				
				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 
					
					<cftry>
						
						<cftransaction>
							
							<cfloop query="rs">
								
								<!--- 检查考试种类 --->
								<cfscript>

									sql = "SELECT 1 FROM t_cet WHERE exam_id = :examId ";

									queryObj = new Query( datasource=application.dnsMaster );
									queryObj.addParam( name="examId", value=rs['语言级别'][rs.currentRow], cfsqltype="cf_sql_char" );

									rs_cet = queryObj.execute( sql=sql ).getResult();

								</cfscript>

								<cfif rs_cet.recordCount >

										<!--- 检查学号 --->
										<cfscript>
											
											sql = "SELECT 1 FROM t_student WHERE stu_id = :studentId ";

											queryObj = new Query( datasource=application.dnsMaster );
											queryObj.addParam( name="studentId", value=rs['学号'][rs.currentRow], cfsqltype="cf_sql_varchar" );

											rs_student = queryObj.execute( sql=sql ).getResult();
											
										</cfscript>

										<cfif rs_student.recordCount >
										
												<!--- 检查成绩分数 --->
												<cfif isNumeric( rs['总分'][rs.currentRow] ) and isNumeric( rs['听力分数'][rs.currentRow] ) and isNumeric( rs['阅读分数'][rs.currentRow] ) and isNumeric( rs['写作分数'][rs.currentRow] ) and isNumeric( rs['综合测试分数'][rs.currentRow] ) >
												
														<!--- 插入或更新成绩信息 --->
														<cfscript>
															
															sql = "SELECT 1 FROM t_cet_scroll WHERE scroll_id = :scrollId ";
															
															queryObj = new Query( datasource=application.dnsMaster );
															queryObj.addParam( name="scrollId", value=rs['准考证'][rs.currentRow], cfsqltype="cf_sql_varchar" );

															rs_scroll = queryObj.execute( sql=sql ).getResult();
															
														</cfscript>
														
														<cfif rs_scroll.recordCount eq 0 >
														
																<!--- 创建新成绩 --->
																<cfscript>
																	
																	sql = "INSERT INTO t_cet_scroll 
																			( scroll_id, exam_id, stu_id, scroll, listening, reading, writing, integration, absent ) 
																			VALUES 
																			( :scrollId, :examId, :studentId, :scroll, :listening, :reading, :writing, :integration, :absent ) ";
																	
																	queryObj = new Query( datasource=application.dnsMaster );
																	queryObj.addParam( name="examId", value=rs['语言级别'][rs.currentRow], cfsqltype="cf_sql_char" );
																	queryObj.addParam( name="scrollId", value=rs['准考证'][rs.currentRow], cfsqltype="cf_sql_varchar" );
																	queryObj.addParam( name="studentId", value=rs['学号'][rs.currentRow], cfsqltype="cf_sql_varchar" );
																	
																	queryObj.addParam( name="scroll", value=rs['总分'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="listening", value=rs['听力分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="reading", value=rs['阅读分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="writing", value=rs['写作分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="integration", value=rs['综合测试分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	
																	if ( rs['缺考'][rs.currentRow] eq "缺" ) { 
																		queryObj.addParam( name="absent", value="1", cfsqltype="cf_sql_char" );
																	} 
																	else {
																		queryObj.addParam( name="absent", value="0", cfsqltype="cf_sql_char" );
																	}

																	queryObj.execute( sql=sql );
																	
																	cetInsert++;

																</cfscript>

															<cfelse>
															
																<!--- 更新成绩 --->
																<cfscript>
																	
																	sql = "UPDATE t_cet_scroll 
																			SET 
																				exam_id = :examId, 
																				stu_id = :studentId, 
																				scroll = :scroll, 
																				listening = :listening, 
																				reading = :reading, 
																				writing = :writing, 
																				integration = :integration, 
																				absent =  :absent 
																			WHERE 
																				scroll_id = :scrollId ";
																	
																	
																	queryObj = new Query( datasource=application.dnsMaster );
																	queryObj.addParam( name="examId", value=rs['语言级别'][rs.currentRow], cfsqltype="cf_sql_char" );
																	queryObj.addParam( name="scrollId", value=rs['准考证'][rs.currentRow], cfsqltype="cf_sql_varchar" );
																	queryObj.addParam( name="studentId", value=rs['学号'][rs.currentRow], cfsqltype="cf_sql_varchar" );
																	
																	queryObj.addParam( name="scroll", value=rs['总分'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="listening", value=rs['听力分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="reading", value=rs['阅读分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="writing", value=rs['写作分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	queryObj.addParam( name="integration", value=rs['综合测试分数'][rs.currentRow], cfsqltype="cf_sql_integer" );
																	
																	if ( rs['缺考'][rs.currentRow] eq "缺" ) { 
																		queryObj.addParam( name="absent", value="1", cfsqltype="cf_sql_char" );
																	} 
																	else {
																		queryObj.addParam( name="absent", value="0", cfsqltype="cf_sql_char" );
																	}

																	queryObj.execute( sql=sql );
																	
																	cetUpdate++;
																	
																</cfscript>

														</cfif>

													<cfelse>
														<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 总分 <cfoutput>#rs['总分'][rs.currentRow]#</cfoutput> 子项 <cfoutput>#rs['听力分数'][rs.currentRow]# / #rs['阅读分数'][rs.currentRow]# / #rs['写作分数'][rs.currentRow]# / #rs['综合测试分数'][rs.currentRow]#</cfoutput> 成绩不是数字</p>
												</cfif>
										
											<cfelse>
												<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 在系统中没有匹配记录</p>
										</cfif>
								
									<cfelse>
										<p class="error">学号 <cfoutput>#rs['学号'][rs.currentRow]#</cfoutput> 语言级别 <cfoutput>#rs['语言级别'][rs.currentRow]#</cfoutput> 在系统中没有匹配记录</p>
								</cfif>
								
							</cfloop>
						
						</cftransaction>
						
						<cfcatch type="any">
							<cfdump var="#cfcatch#">
							<p class="error">数据导入错误... <cfoutput>#cfcatch.Message#</cfoutput></p>
						</cfcatch>
					</cftry>
					
				</cfif>
				
			</cfif>
			
			
		</cfif> 
		
		<p>新增成绩数据 [<cfoutput>#cetInsert#</cfoutput>] 条, 更新现有成绩数据 [<cfoutput>#cetUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL('cetImport', targetArgs)#</cfoutput>">刷新CET成绩数据</a>
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>

		